Le package dbExplorer

1 A quoi sert le package dbExplorer ?

Un package R qui lance une application Shiny qui a plusieurs objectifs :

• Explorer rapidement les données présentes dans une base de données
• Explorer rapidement le contenu des tables au format Parquet sur Cerise
• Faire des manipulations de 1er niveau sur ces données

=> Un package développé et maintenu par Adam Marsal du SSM Justice.

2 Installation du package depuis Cerise

# install.packages("remotes")
remotes::install_git("https://gitlab.forge.agriculture.rie.gouv.fr/ssp/bmis/packages/dbexplorer",
                     dependencies = T,
                     git = "external")

Comme n’importe quel autre package R, {dbExplorer} doit être chargé avec :

library(dbExplorer)

Avant d’explorer ce package, 2 concepts théoriques sont nécessaires.

3 Les “Background jobs” de RStudio

Les “Background Jobs” dans RStudio sont une fonctionnalité qui permet d’exécuter des scripts R en arrière-plan sans bloquer l’interface principale de RStudio.

Quelques caractéristiques :

• Une exécution indépendante : le job s’exécute dans un processus séparé de la session principale (non bloquant).
  
• Suivi de l’exécution : une fenêtre dédiée dans RStudio qui permet de consulter la progression, les messages et l’état du job (traçabilité).
• Une reproductibilité garantie : les jobs relancent le script tel qu’il est au moment de l’appel dans un environnement propre. Le job s’exécute de manière isolé sans polluer l’environnement global.

4 Exemple de Background jobs (1/3)

Soit le script simple suivant :

library(dplyr)

Sys.sleep(5)
tab_agregee <- iris |> 
  summarise(Moy_pet_long = mean(Petal.Length, na.rm = TRUE), .by = Species)
Sys.sleep(5)

print("Pgm terminé")

Ce script peut être lancé via un background job en cliquant sur le bouton "Start Background job".

5 Exemple de Background jobs (2/3)

La fenêtre suivante s’affiche :

6 Exemple de Background jobs (3/3)

En fonction des choix effectués au lancement du background job, les résultats seront (ou pas) disponibles dans l’environnement global de la session R.

Si vous avez choisi de stocker les résultats dans un objet “results”, voici comment y accéder :

7 Structure d’une base de données relationnelles

Les bases de données relationnelles sont structurées de manière hiérarchique et logique.

• La Base de donnée (BDD) : c’est l’entité principale qui contient l’ensemble des données
• Le schéma : c’est une structure logique à l’intérieur d’une BDD. Il sert à organiser les objets. Il peut être vu comme un dossier dans la BDD
• Les tables : ce sont les éléments centraux où les données sont stockées
• Les vues : ce sont des représentations virtuelles de tables (créées au moment des requêtes)

Exemple de structure :

Base de données : vente_en_ligne
│
├── Schéma : public
│   ├── Table : clients
│   ├── Table : commandes
│   └── Table : produits
│
└── Schéma : archive
    └── Table : commandes_2020

8 Se connecter à une BDD sans dbExplorer (1/3)

Exemple avec BDD PostgreSQL :

library(connections)
library(RPostgres)

### Chargement des infos de connection
infos_connection_prod <- read_delim("infos_connection.csv", 
                                    delim = ";", escape_double = FALSE, trim_ws = TRUE,
                                    show_col_types = FALSE) %>% 
  filter(ENV == "PROD")

### Connexion à la BDD
con <- connection_open(
    Postgres(), 
    user = infos_connection_prod[["USER"]],
    password = rawToChar(openssl::base64_decode(infos_connection_prod[["PWD"]])),
    host = infos_connection_prod[["DB_URL"]], 
    dbname = infos_connection_prod[["DATABASE"]],
    port = infos_connection_prod[["PORT"]])

9 Se connecter à une BDD sans dbExplorer (2/3)

=> La structure de la BDD est visible dans l’onglet "Connections" de RStudio :

10 Se connecter à une BDD sans dbExplorer (3/3)

11 Explorer une base de données PostgreSQL (1/N)

## Installation du package 
remotes::install_git("https://gitlab.forge.agriculture.rie.gouv.fr/ssp/bmis/packages/dbexplorer",
                     dependencies = T,
                     git = "external")

## Création du fichier de connexion à postgre SQL 
connector_file <- "./R/dbExplorer-connector/dbExplorerPgConnector.R"
dbExplorer:::createPostgreSQLConnector(host = "postgresql-164525.projet-sortie-sas",
                                       dbname = "dvf",
                                       port = 5432,
                                       file=connector_file)

## Lancement de l'application sur ce fichier de connexion. 
ExplorerDonnees(connectorFile = connector_file)

=> Un “background job” se lance dans RStudio et une application shiny s’ouvre dans le navigateur.

12 Explorer une base de données PostgreSQL (2/N)

Entrer le login & mot de passe d’accès à la BDD :

13 Explorer une base de données PostgreSQL (3/N)

14 Démonstration

Démo sur Cerise ! (voir rép. dbExplorer_test)

15 Quelques raccourcis/astuces utiles !

• Maintenir la touche “Tab” enfoncée pour sélectionner les colonnes interactivement
  
• Pour filtrer les lignes, cocher la case “Cliquer pour filtrer” puis :
  • Cliquer une fois pour appliquer le filtre sur les lignes de la table
  • Cliquer deux fois pour appliquer la négation du filtre sur les lignes de la table
• Pour télécharger les jeux de données au format csv, cliquer en haut à droite sur le bouton “télécharger” ou faire Ctrl + S.

16 Création de fichiers parquet à partir d’une BDD (1/2)

### Connexion à la BDD
con <- connection_open(
    Postgres(), 
    user = infos_connection_prod[["USER"]],
    password = rawToChar(openssl::base64_decode(infos_connection_prod[["PWD"]])),
    host = infos_connection_prod[["DB_URL"]], 
    dbname = infos_connection_prod[["DATABASE"]],
    port = infos_connection_prod[["PORT"]])

Appel de la fonction exportPgToParquet.R :

source("R/exportPgToParquet.R")

17 Création de fichiers parquet à partir d’une BDD (2/2)

# Export de la table categorie_juridique
exportPgToParquet(con,"categorie_juridique")
# Export de la table entite_surface
exportPgToParquet(con,"entite_surface")

18 Explorer une base de données SQLite

library(RSQLite)
connector_file <- "R/dbExplorer-connector/connector_sqlite.R"
dbExplorer::ExplorerDonnees(connectorFile = "R/dbExplorer-connector/connector_sqlite.R")

19 Explorer des fichiers parquets

dbExplorer::ExplorerDossiers("~/CERISE/03-Espace-de-Diffusion/030_Structures_exploitations/3020_Recensements/RA_2010/")

20 Pour en savoir plus

• Fiche utilitr sur l’utilisation des bases de données avec R.